# ==============================
# _*_ coding:utf-8 _*_
# _*_ by:zizle     _*_
# _*_ at:20230423  _*_
# ==============================

from fastapi import APIRouter, Query
from utils import datetime_utils
from db_utils import BaseDatabaseConnection
from response import APIResponse


class FutureSpot:
    def __init__(self, start=None, end=None, code=None):
        self.code = code
        self.start = start
        if self.start:
            self.start = datetime_utils.auth_yyyymmdd(start, obj=True)
            self.end = datetime_utils.auth_yyyymmdd(end, obj=True)
            if not self.end:
                self.end = self.start
            assert self.start
        else:
            self.start, self.end = None, None

    def get_basic_interval_sql(self):
        start_date = datetime_utils.to_yyyy_mm_dd(self.start)
        end_date = datetime_utils.to_yyyy_mm_dd(self.end)
        sql = "SELECT * FROM quote_future_spot WHERE is_active=1 AND date>=%s AND date<=%s"
        prm = [start_date, end_date]
        if self.code:
            sql += ' AND code=%s;'
            prm.append(self.code)
        return sql, prm

    def get_basic_latest_sql(self):
        sql = "SELECT * FROM quote_future_spot WHERE is_active=1 AND date=(SELECT MAX(date) FROM quote_future_spot)"
        prm = None
        if self.code:
            sql += ' AND code=%s;'
            prm = [self.code]
        return sql, prm

    def get_position_interval_sql(self):
        start_date = datetime_utils.to_yyyy_mm_dd(self.start)
        end_date = datetime_utils.to_yyyy_mm_dd(self.end)
        sql = "SELECT * FROM quote_future_position WHERE is_active=1 AND date>=%s AND date<=%s"
        prm = [start_date, end_date]
        if self.code:
            sql += ' AND code=%s;'
            prm.append(self.code)
        return sql, prm

    def get_position_latest_sql(self):
        sql = "SELECT * FROM quote_future_position WHERE is_active=1 AND date=(SELECT MAX(date) FROM quote_future_position)"
        prm = None
        if self.code:
            sql += ' AND code=%s;'
            prm = [self.code]
        return sql, prm

    def future_spot_basic(self):
        if self.start:
            sql, prm = self.get_basic_interval_sql()
        else:
            sql, prm = self.get_basic_latest_sql()

        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, )
            results = list(conn.cursor.fetchall())
        for row in results:
            row['basic'] = round(row['spot'] - row['close'], 2)
        res_data = {
            'basic': results,
            'quote_date': results[0]['date'] if len(results) > 0 else ''
        }
        return APIResponse.success(data=res_data)

    def future_position(self):
        if self.start:
            sql, prm = self.get_position_interval_sql()
        else:
            sql, prm = self.get_position_latest_sql()

        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, )
            results = list(conn.cursor.fetchall())
        for row in results:
            row['net_position'] = int(row['long'] - row['short'])
        res_data = {
            'position': results,
            'quote_date': results[0]['date'] if len(results) > 0 else ''
        }
        return APIResponse.success(data=res_data)


futures_api = APIRouter()


# 基差表
@futures_api.get('/spotBasic/')
def get_future_spot_basic(start: str = Query(None), end: str = Query(None), code: str = Query(None)):
    return FutureSpot(start=start, end=end, code=code).future_spot_basic()


# 持仓表
@futures_api.get('/position/')
def get_future_position(start: str = Query(None), end: str = Query(None), code: str = Query(None)):
    return FutureSpot(start=start, end=end, code=code).future_position()
